import 'package:sqlite3/sqlite3.dart';
import '../model/model_language.dart';
import '../model/model_user_album_media.dart';
import '../tools/tools_database.dart';

class DaoUserAlbumMedia {
  static Future<int> insert(ModelUserAlbumMedia l) async {
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
    INSERT INTO 
      p001_user_album_media(
      album_id,file_name,
      sentence_qty,finish_qty,
      word_qty,speed,level,last_study_time
      ,gmt_create,gmt_modified,remark)
    VALUES
      (?,?,
      ?,?,
      ?,?,?,?,
      ?,?,?
      )
    ;
    ''';
    final stmt = db.prepare(sql);
    DateTime now = DateTime.now();
    int gmt_create = now.millisecondsSinceEpoch;
    stmt
      ..execute([
        l.userAlbumId,
        l.fileName,
        l.sentenceQty,
        l.finishQty,
        l.wordQty,
        l.speed,
        l.level,
        l.lastStudyTime,
        gmt_create,
        gmt_create,
        l.remark
      ]);

    // 3. 关闭连接
    db.dispose();
    return 0;
  }

  static Future<int> deleteById(int id) async {
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      delete from 
        p001_user_album_media 
      where 
        id = ?;
    ''';
    final stmt = db.prepare(sql);
    stmt..execute([id]);

    // 3. 关闭连接
    db.dispose();
    return 0;
  }

  static Future<int> updateById(ModelUserAlbumMedia l) async {
    print(l);
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      update 
        p001_user_album_media
      set
        album_id=?,file_name=?,
        sentence_qty=?,finish_qty=?,
        word_qty=?,speed=?,level=?,
        last_study_time=?,
        gmt_modified = ?,
        remark = ?
      where
        id = ?
      ;
    ''';

    final stmt = db.prepare(sql);
    try {
      int gmt_modified = DateTime.now().millisecondsSinceEpoch;
      stmt
        ..execute([
          l.userAlbumId,
          l.fileName,
          l.sentenceQty,
          l.finishQty,
          l.wordQty,
          l.speed,
          l.level,
          l.lastStudyTime,
          gmt_modified,
          l.remark,
          l.id
        ]);
      print("执行成功");
    } catch (e) {
      print("出现异常");
      print(e);
    }

    // 3. 关闭连接
    db.dispose();
    return 0;
  }

  static Future<List<ModelUserAlbumMedia>> findAllByAlbumId(int albumId) async {
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_user_album_media
      where
        album_id = ?
      order by 
        file_name
      ;
    ''';
    ResultSet rs = db.select(sql, [albumId]);
    Iterator<Row> i = rs.iterator;
    List<ModelUserAlbumMedia> list = [];
    while (i.moveNext()) {
      Row r = i.current;
      print(r);

      ModelUserAlbumMedia w = new ModelUserAlbumMedia();
      w.id = r["id"];
      w.userAlbumId = r["album_id"];
      w.fileName = r["file_name"];
      w.sentenceQty = r["sentence_qty"];
      w.finishQty = r["finish_qty"];
      w.wordQty = r["word_qty"];
      w.speed = r["speed"];
      w.level = r["level"];
      w.lastStudyTime = r["last_study_time"];
      w.gmtCreate = r["gmt_create"];
      w.gmtModified = r["gmt_modified"];
      w.remark = r["remark"];

      list.add(w);
    }

    // 3. 关闭连接
    db.dispose();
    return list;
  }

  static Future<List<ModelUserAlbumMedia>> findAll() async {
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_user_album_media
      ;
    ''';
    ResultSet rs = db.select(sql);
    Iterator<Row> i = rs.iterator;
    List<ModelUserAlbumMedia> list = [];
    while (i.moveNext()) {
      Row r = i.current;
      print(r);

      ModelUserAlbumMedia w = new ModelUserAlbumMedia();
      w.id = r["id"];
      w.userAlbumId = r["album_id"];
      w.fileName = r["file_name"];
      w.sentenceQty = r["sentence_qty"];
      w.finishQty = r["finish_qty"];
      w.wordQty = r["word_qty"];
      w.speed = r["speed"];
      w.level = r["level"];
      w.lastStudyTime = r["last_study_time"];
      w.gmtCreate = r["gmt_create"];
      w.gmtModified = r["gmt_modified"];
      w.remark = r["remark"];

      list.add(w);
    }

    // 3. 关闭连接
    db.dispose();
    return list;
  }

  static Future<ModelUserAlbumMedia?> findById(int id) async {
    ModelUserAlbumMedia? m = null;

    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_user_album_media
      where
        id = ?
      ;
    ''';
    ModelUserAlbumMedia? w = null;
    ResultSet rs = db.select(sql, [id]);
    Iterator<Row> i = rs.iterator;
    if (i.moveNext()) {
      Row r = i.current;
      print(r);

      w = new ModelUserAlbumMedia();
      w.id = r["id"];
      w.userAlbumId = r["album_id"];
      w.fileName = r["file_name"];
      w.sentenceQty = r["sentence_qty"];
      w.finishQty = r["finish_qty"];
      w.wordQty = r["word_qty"];
      w.speed = r["speed"];
      w.level = r["level"];
      w.lastStudyTime = r["last_study_time"];
      w.gmtCreate = r["gmt_create"];
      w.gmtModified = r["gmt_modified"];
      w.remark = r["remark"];
    }

    // 3. 关闭连接
    db.dispose();

    return m;
  }

  static Future<void> deleteByAlbumId(int id) async {
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      delete from 
        p001_user_album_media 
      where 
        album_id = ?;
    ''';
    final stmt = db.prepare(sql);
    stmt..execute([id]);

    // 3. 关闭连接
    db.dispose();
  }
}
